package Messages;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import Gui.AddBook;
import Gui.TheBookIsCopy;
import ocsf.server.ConnectionToClient;
import server.SQLConnection;
/**
 * this class implements the librarian add book to db
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class MessageAddBookDB {

	private ConnectionToClient client;
	static int ID=0 ;
	static int Copybookid;
	static int numcopyrs;
	static int row1;
/**
 * constractor of this class 
 * @param get the client
 */
	public MessageAddBookDB(ConnectionToClient client) {
		// TODO Auto-generated constructor stub
	this.client= client;
	}
	
	/**
	 * adding bood to DB 
	 * @param str
	 * @throws IOException
	 * @throws SQLException 
	 */
	public void AddBooktoDB(String[] str) throws IOException, SQLException{

		System.out.println(AddBook.j);
		
		if (str[1].equals("1"))
		{
			try {
				
				
				SQLConnection sc = new SQLConnection();
				PreparedStatement id = sc.getConnection().prepareStatement("SELECT * FROM books Where ID ='"+str[2]+"';");
				PreparedStatement cid = sc.getConnection().prepareStatement("SELECT * FROM bookscopies;");
				PreparedStatement ps = sc.getConnection().prepareStatement("update db1.books set NumberOfCopies = ?  where ID = '"+str[2]+"'");
				PreparedStatement copyps = sc.getConnection().prepareStatement("INSERT INTO bookscopies (CopyId,CopyName,BID,BuyingDate) VALUES(?,?,?,?);");
				PreparedStatement numcopy = sc.getConnection().prepareStatement("SELECT CopyName FROM DB1.bookscopies WHERE CopyName='"+TheBookIsCopy.Copyname+"' ;");

				sc.getConnection().setAutoCommit(true);
				
				ResultSet rs = id.executeQuery();
				rs.next();
				numcopyrs = rs.getInt(8)+1;
				
				ps.setInt(1, numcopyrs);
				ps.executeUpdate();
				
				ResultSet copyrs= cid.executeQuery();
				row1 = 1;
				while(copyrs.next()){
					int temp = copyrs.getInt(1);
						if(row1>temp)
							row1=row1;
							else{
							row1=temp;
							}
				
				}
				
				copyps.setInt(1, this.row1+1);
				copyps.setString(2,rs.getString(2));
				copyps.setString(3,rs.getString(1));
				copyps.setString(4, str[3]);

				copyps.executeUpdate();
				
			
				copyps.close();
				
				
				
			
			client.sendToClient("AddBookDB");


				
			}
			
		

		 catch (SQLException e)
		{	e.printStackTrace();}


	}
			
			
		
			
			
	else if (str[1].equals("0"))
	{
		SQLConnection ss = new SQLConnection();
		PreparedStatement ck = ss.getConnection().prepareStatement("SELECT books.CatalogNumber FROM books where books.CatalogNumber='"+str[9]+"';");
		ResultSet tst= ck.executeQuery();
		if(!tst.next())
		{
			try {
			
			SQLConnection sc = new SQLConnection();
			PreparedStatement id = sc.getConnection().prepareStatement("SELECT * FROM books;");
			PreparedStatement cid = sc.getConnection().prepareStatement("SELECT * FROM bookscopies;");
			PreparedStatement ps = sc.getConnection().prepareStatement("INSERT INTO books (ID,Title,Author,EditionNumber,Printdate,Description,CatalogNumber,NumberOfCopies,PlaceOnShelf,HighlyWanted) VALUES(?,?,?,?,?,?,?,?,?,?);");
			PreparedStatement copyps = sc.getConnection().prepareStatement("INSERT INTO bookscopies (CopyId,CopyName,BID,BuyingDate) VALUES(?,?,?,?);");
			PreparedStatement numcopy = sc.getConnection().prepareStatement("SELECT CopyName FROM DB1.bookscopies WHERE CopyName='"+str[1]+"' ;");
			PreparedStatement bcin = sc.getConnection().prepareStatement("INSERT INTO bookscategory (BookID,CategoryName) VALUES(?,?);");
		sc.getConnection().setAutoCommit(true);
			
		
		ResultSet rs= id.executeQuery();
			 row1 = 0;
			while(rs.next()){
				int temp = rs.getInt(1);
					if(row1>temp)
						row1=row1;
						else{
						row1=temp;
						}
			
			}
			

			ResultSet numcopyrs = numcopy.executeQuery();
			
			
			numcopyrs.last();
			MessageAddBookDB.numcopyrs=numcopyrs.getRow();
			Copybookid=this.row1+1;
			
			ps.setInt(1,Copybookid);
			ps.setString(2, str[2]);
			ps.setString(3, str[3]);
			ps.setString(4, str[7]);
			ps.setString(5, str[8]);
			ps.setString(6, str[11]);
			ps.setString(7, str[9]);
			ps.setInt(8, 1);
			ps.setString(9, str[5]);
			ps.setString(10, str[6]);
		
	
			ps.executeUpdate();
			
		
		ps.close();
		
		
		
		
				bcin.setInt(1, Copybookid);
				bcin.setString(2, str[4]);
				bcin.executeUpdate();	
				bcin.close();
		
		
		
		
		ResultSet copyrs= cid.executeQuery();
		 row1 = 1;
		while(copyrs.next()){
			int temp = copyrs.getInt(1);
				if(row1>temp)
					row1=row1;
					else{
					row1=temp;
					}
		
		}
		
		
		copyps.setInt(1, this.row1+1);
		copyps.setString(2, str[2]);
		copyps.setInt(3,Copybookid);
		copyps.setString(4, str[10]);

		copyps.executeUpdate();
		
	
		copyps.close();
			
		
		
		
				
		
		
				
		
		
		
		
		
		

		client.sendToClient("AddBookDB");


			
		}
		
		
	

	 catch (SQLException e)
	 	{	
		 e.printStackTrace();
		 }
	}
		else
		{
			client.sendToClient("FailAddBook");
		}


}

	
	}
}
